﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using FileTransferServiceReference;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Data;
using NhbHelper;
using NhbHelper.Entities;
using NHibernate;
using NhbHelper.DataAccess;


public partial class UploadSourceFile : System.Web.UI.UserControl
{
    public event EventHandler SaveCompleted;

    private static SavedReconciliation m_rec;
    private static List<TableColumn> m_LeftTableColumns;
    private static List<TableColumn> m_RightTableColumns;
    private static List<TableColumn> m_OutputTableColumns;
    private static int m_RecId = 0;
    protected static string m_FileNameLeft;
    protected static string m_FileNameRight;
    
    protected ISession m_session;


    public int GetUniqueRecID
    {
        get { return m_RecId; }
        set { m_RecId = value; }                          
    }
    public string RightFileName
    {
        get { return m_FileNameRight; }
        set { m_FileNameRight = value; }
    }
    public string LeftFileName
    {
        get { return m_FileNameLeft; }
        set { m_FileNameLeft = value; }
    }

    public void RefreshRecNumLabel()
    {
        lblId.Text = GetUniqueRecID.ToString();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        

        if (Parent.Visible)
        {
            if (m_rec == null)
            {               
                m_rec = new SavedReconciliation();
                m_LeftTableColumns = new List<TableColumn>();
                m_RightTableColumns = new List<TableColumn>();
                m_OutputTableColumns = new List<TableColumn>();
            }                           
            //RefreshDisplay();
        }
    }

    

    //this procedure needs to kick off a parsing operation against the file uploaded.
    //which will return a list of DataStructure objects.
    //which the user needs to confirm/edit.
      
    protected void LeftUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            System.IO.FileInfo fileInfo = new System.IO.FileInfo(FileUpload1.PostedFile.FileName);
            LeftFileName = "Left.csv";// fileInfo.Name;
            FileTransferServiceReference.ITransferService clientUpload = new FileTransferServiceReference.TransferServiceClient();
            FileTransferServiceReference.RemoteFileInfo uploadRequestInfo = new RemoteFileInfo();

            using (System.IO.FileStream stream = new System.IO.FileStream(FileUpload1.PostedFile.FileName, System.IO.FileMode.Open, System.IO.FileAccess.Read))
            {
                uploadRequestInfo.FileName = FileUpload1.FileName;
                uploadRequestInfo.Length = fileInfo.Length;
                uploadRequestInfo.FileByteStream = stream;
                uploadRequestInfo.UploadLocation = @"C:\TEMP\working\WebSite1\Clients\Alpha\dropzone";
                clientUpload.UploadFile(uploadRequestInfo);
            }

            //Kickoff_Parsing_Operation
            m_session = NHibernateHttpModule.CurrentSession;
            IList<HResult> myList = m_session.GetNamedQuery("DetermineSourceStructure").List<HResult>();                     
            string test = myList[0]._Message; 
            //the above will return "number|varchar2|varchar2|number|varchar2|number" which we use to constitute a DataStructureObj 
            //and display it to the user below the file just uploaded.
            int i = 0;
            foreach (string str_input in test.Split('|'))
            {
                LoadDataStructuresLeft(i, str_input, 100, false);                      
                m_LeftTableColumns.Add(new TableColumn("ColumnName" + i,
                                                        50,
                                                        DetermineColumnType(str_input),
                                                        TABLE_TYPE.Left,
                                                        false
                                                        ));
                i++;
            }
            RefreshDisplay();
        }
    }
    protected void RightUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload2.HasFile)
        {
            System.IO.FileInfo fileInfo = new System.IO.FileInfo(FileUpload2.PostedFile.FileName);
            RightFileName = "Right.csv";// fileInfo.Name;
            FileTransferServiceReference.ITransferService clientUpload = new FileTransferServiceReference.TransferServiceClient();
            FileTransferServiceReference.RemoteFileInfo uploadRequestInfo = new RemoteFileInfo();

            using (System.IO.FileStream stream = new System.IO.FileStream(FileUpload2.PostedFile.FileName, System.IO.FileMode.Open, System.IO.FileAccess.Read))
            {
                uploadRequestInfo.FileName = FileUpload2.FileName;
                uploadRequestInfo.Length = fileInfo.Length;
                uploadRequestInfo.FileByteStream = stream;
                uploadRequestInfo.UploadLocation = @"C:\TEMP\working\WebSite1\Clients\Alpha\dropzone";
                clientUpload.UploadFile(uploadRequestInfo);
            }

            //Kickoff_Parsing_Operation
            m_session = NHibernateHttpModule.CurrentSession;
            IList<HResult> myList = m_session.GetNamedQuery("DetermineSourceStructure").List<HResult>();
            string test = myList[0]._Message; 
            //the above will return "number|varchar2|varchar2|number|varchar2|number" which we use to constitute a DataStructureObj 
            //and display it to the user below the file just uploaded.
            int i = 0;
            foreach (string str_input in test.Split('|'))
            {               
                LoadDataStructuresRight(i,str_input,100,false);                                
                m_RightTableColumns.Add(new TableColumn("ColumnName" + i,
                                                       50,
                                                       DetermineColumnType(str_input),
                                                       NhbHelper.Entities.TABLE_TYPE.Right,
                                                       false
                                                       ));
               
                i++;
            }
            RefreshDisplay();
            
        }
    }

    private NhbHelper.Entities.COLUMN_TYPE DetermineColumnType(string s_source)
    {
        switch (s_source)
                {
                    case "number":
                        {
                            return NhbHelper.Entities.COLUMN_TYPE.Number;                            
                        }
                    case "varchar2":
                        {
                            return NhbHelper.Entities.COLUMN_TYPE.Varchar2;                            
                        }
                    case "date":
                        {
                            return NhbHelper.Entities.COLUMN_TYPE.Date;                         
                        }
                    default:
                        {
                            return NhbHelper.Entities.COLUMN_TYPE.Date;                          
                        }
                }            
    }
    private void RefreshDisplay()
    {
        
        //right
        this.RightFilePH.Controls.Clear();
        foreach (DataStuctureObj ds in m_rec.RightTableStructure)
        {
            ASP.DataStructure DS = (ASP.DataStructure)LoadControl("~/WebControls/DataStructure.ascx");
            RightFilePH.Controls.Add(DS);
        }
       
        //left
        this.LeftFilePH.Controls.Clear();
        foreach (DataStuctureObj ds in m_rec.LeftTableStructure)
        {
            ASP.DataStructure DS = (ASP.DataStructure)LoadControl("~/WebControls/DataStructure.ascx");
            LeftFilePH.Controls.Add(DS);
        }

        //output
        OutputReportPH.Controls.Clear();
        m_OutputTableColumns.Clear();
            //add in the Left
        int i = 0;
            foreach (TableColumn tc in m_LeftTableColumns)
            {
                ASP.OutputFormat of = (ASP.OutputFormat)LoadControl("~/WebControls/OutputFormat.ascx");
                OutputReportPH.Controls.Add(of);
                m_OutputTableColumns.Add(new TableColumn("ColumnName_L_" + i,
                                                      50,
                                                      tc.Col_Type,
                                                      TABLE_TYPE.Output,
                                                      false
                                                      ));
                i++;
            }
            //add in the Right
            foreach (TableColumn tc in m_RightTableColumns)
            {
                ASP.OutputFormat of = (ASP.OutputFormat)LoadControl("~/WebControls/OutputFormat.ascx");
                OutputReportPH.Controls.Add(of);
                m_OutputTableColumns.Add(new TableColumn("ColumnName_R_" + i,
                                                      50,
                                                      tc.Col_Type,
                                                      TABLE_TYPE.Output,
                                                      false
                                                      ));
                i++;
            }
    }
    private void LoadDataStructuresRight(int i, string input_type, int lenght, bool iskey)
    {
        DataStuctureObj ds = new DataStuctureObj(i, input_type, 1, false);
        m_rec.RightTableStructure.Add(ds);        
    }

    private void LoadDataStructuresLeft(int i, string input_type, int lenght, bool iskey)
    {
        DataStuctureObj ds = new DataStuctureObj(i, input_type, 1, false);
        m_rec.LeftTableStructure.Add(ds);        
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {                
        
        //Left
        string p_fields_left = RetrieveTableColumns(m_LeftTableColumns,true);       
        string p_tablespace = "RECRUNNER";
        string p_tablename  = "zz_" + lblId.Text +  "_table_left" ;
        //we need to iterate over all columns and persist those identified as key fields.
        string p_constraint = "";
        CreateTable(p_fields_left, p_tablename, p_tablespace, p_constraint);
        //create the sqlloader scripts for the files already loaded
        CreateSQLLoaderScript(LeftFileName, "BAD_" + LeftFileName, p_tablename, RetrieveTableColumns(m_LeftTableColumns,false), "clientDirectoryName");
        CBatchRunner.ExecuteBatch(p_tablename + ".bat", "test", "TestDeviceFilter");
        //Right
        string p_fields_right = RetrieveTableColumns(m_RightTableColumns,true);
        p_tablespace = "RECRUNNER";
        p_tablename = "zz_"+ lblId.Text +"_table_right"; 
        //we need to iterate over all columns and persist those identified as key fields.
        p_constraint = "";
        CreateTable(p_fields_right, p_tablename, p_tablespace, p_constraint);
        //create the sqlloader scripts for the files already loaded
        CreateSQLLoaderScript(RightFileName, "BAD_" + RightFileName, p_tablename, RetrieveTableColumns(m_RightTableColumns, false), "clientDirectoryName");

        CBatchRunner.ExecuteBatch(p_tablename + ".bat","test", "TestDeviceFilter");

        //Output
        string p_fields_output = RetrieveTableColumns(m_OutputTableColumns,true);
        p_tablespace = "RECRUNNER";
        p_tablename = "zz_"+ lblId.Text + "_table_output"; 
        //we need to iterate over all columns and persist those identified as key fields.
        p_constraint = "";
        CreateTable(p_fields_output, p_tablename, p_tablespace, p_constraint);

        //save the actual rec
        SaveRec();
       
        //bubble our event completed to homepage
        OnSaveCompleted(e);        

        ClearAllColumnsandPanels();
             
    }
    
    protected void CreateSQLLoaderScript(string s_sourceFile, string s_badFile, string s_target, string s_colNames, string s_clientArea)
    {
        m_session = NHibernateHttpModule.CurrentSession;
        IQuery iq = m_session.CreateSQLQuery("call pkg_utility.createsqlloader(:p_source_file,:p_bad_txt,:p_target,:p_cols,:p_client_dir)");
        iq.SetString("p_source_file", s_sourceFile);
        iq.SetString("p_bad_txt", s_badFile);
        iq.SetString("p_target", s_target);
        iq.SetString("p_cols", s_colNames);
        iq.SetString("p_client_dir", "DIR_CLIENT_ALPHA");   
        iq.ExecuteUpdate();
        m_session.Flush();   

    }
    protected void OnSaveCompleted(EventArgs e)
    {
        if (SaveCompleted != null)
        {
            SaveCompleted(this, e);
        }
    }          
    private void SaveRec()
    {        
        m_session = NHibernateHttpModule.CurrentSession;
        IQuery iq = m_session.CreateSQLQuery("call pkg_utility.insertreconciliation(:p_id,:p_name,:p_lastrun,:p_tbl_left,:p_tbl_right,:p_tbl_out,:p_rec_lnk)");
        iq.SetInt32("p_id", Convert.ToInt32(lblId.Text));
        iq.SetString("p_name", txtRecName.Text);
        iq.SetDateTime("p_lastrun", DateTime.UtcNow);
        iq.SetString("p_tbl_left", "zz_"+ lblId.Text +"_table_left");
        iq.SetString("p_tbl_right", "zz_"+ lblId.Text +"_table_right");
        iq.SetString("p_tbl_out", "zz_" + lblId.Text + "_table_output");
        iq.SetInt32("p_rec_lnk", -1);
        iq.ExecuteUpdate();
        m_session.Flush();   
    }
    private void ClearAllColumnsandPanels()
    {
        LeftFilePH.Controls.Clear();
        RightFilePH.Controls.Clear();
        OutputReportPH.Controls.Clear();
        m_LeftTableColumns.Clear();
        m_RightTableColumns.Clear();
        m_OutputTableColumns.Clear();
    }
    private string RetrieveTableColumns(List<TableColumn> source, bool includeColumnTypes)
    {
        StringBuilder sb_fields = new StringBuilder();
        foreach (TableColumn t in source)
        {
            switch (t.Col_Type)
            {
                case COLUMN_TYPE.Number:
                    {
                        if (includeColumnTypes)
                            sb_fields.Append(t.Col_Name + ' ' + t.Col_Type + ',');
                        else
                            sb_fields.Append(t.Col_Name + ',');
                            break;
                    }
                case COLUMN_TYPE.Varchar2:
                    {
                        if(includeColumnTypes)
                            sb_fields.Append(t.Col_Name + ' ' + t.Col_Type + '(' + t.Col_Length + "),");
                        else
                            sb_fields.Append(t.Col_Name + ',');
                    break;
                    }
            }
        }
        return sb_fields.ToString().TrimEnd(','); 
    }
    //we need to forward all the columns for left, right and output to Oracle to create the structures.
    // we will call
    /*
    pkg_utility.createtable(p_fields => :p_fields, --COL1 NUMBER , COL2 VARCHAR2(50) 
                      p_tablename => :p_tablename,
                      p_tablespace => :p_tablespace,
                      p_constraint => :p_constraint);*/
    private void CreateTable(string sfields, string stablename, string stablespace, string sconstraint)
    {
        m_session = NHibernateHttpModule.CurrentSession;
        IQuery iq = m_session.CreateSQLQuery("call pkg_utility.CreateTable(:p_fields,:p_tablename,:p_tablespace,:p_constraint)");
        iq.SetString("p_fields", sfields);
        iq.SetString("p_tablename", stablename);
        iq.SetString("p_tablespace", stablespace);
        iq.SetString("p_constraint", sconstraint);
        iq.ExecuteUpdate();
        m_session.Flush();    
    }
}